/** 
   Drop all tables in CS440 account.
*/

import java.sql.*;
import java.io.*;

public class CleanStudentAccount {

	Connection cnn;
	Statement  stmt, stmt2;
	ResultSet  res, res2;
	String	   objectNames[] = {"TABLE", "VIEW", "INDEX", "PROCEDURE", "FUNCTION", "PACKAGE", "TYPE", "OTHER" };
	int	   numberOfObjects = 0,
		   deletedCounts[] = new int[objectNames.length],
		   undeletedCounts[] = new int[objectNames.length];
	int	   round = 0;
	boolean	   atLeastOneDeleted = true;

	String	url = "jdbc:oracle:thin:@prover.cs.csubak.edu:1521:PROVERDB";
	// String	url = "jdbc:oracle:thin:@cheval.cs.csubak.edu:1521:TEST",
	// String user="cs342", passwd="student2";
	// user="cs440", passwd="student1";

	void clearObjectCounts() {
		for ( int i = 0; i < objectNames.length; i ++ ) {
			deletedCounts[i] = 0; undeletedCounts[i] = 0;
		}
	}

	void printObjectCounts() {
	   int deletedSubtotal = 0, undeletedSubtotal = 0;
	   System.out.println("\n");
	   System.out.println("+=======================================================================================+");
	   System.out.println("|            Round " + ScreenIO.expandString(4, round + "") +
			"                             Number of Objects to remove " +
			ScreenIO.expandString( 4, numberOfObjects + "") + "    |");
	   System.out.println("+=======================================================================================+");
	   System.out.println("|            | TABLE |  VIEW | INDEX | PROC. |  FUN. | PACK. |  TYPE | OTHER | Subtotal |");
	   System.out.println("+---------------------------------------------------------------------------------------+");
	   System.out.print  ("| Del Suc.   |");
	   for ( int i = 0; i < objectNames.length; i ++ ) {
		System.out.print( ScreenIO.expandString(8, deletedCounts[i] + " |") ) ;
		deletedSubtotal += deletedCounts[i];
	   }
	   System.out.print( ScreenIO.expandString(11, deletedSubtotal + " |") ) ;
	   System.out.println();
	   System.out.println("+---------------------------------------------------------------------------------------+");
	   System.out.print  ("| Del Failed |");
	   for ( int i = 0; i < objectNames.length; i ++ ) {
		System.out.print( ScreenIO.expandString(8, undeletedCounts[i] + " |") ) ;
		undeletedSubtotal += undeletedCounts[i];
	   }
	   System.out.print( ScreenIO.expandString(11, undeletedSubtotal + " |") ) ;
	   System.out.println();
	   System.out.println("+---------------------------------------------------------------------------------------+");
	}

	void increaseObjectCounts (int counts[], String type ) {
	   int i = 0;
	   for ( ; i < objectNames.length - 1; i ++ )
		if ( type.equals(objectNames[i]) ) break; 
	   counts[i]++;
        }

	public CleanStudentAccount (String user, String passwd) {
	   try {
		// The following two methods of loading JDBC driver are tested and worked
		// Method 1: call Class.forName(), method 2: call DriverManager.registerDriver().
		// ---------------------------------------------------------------------------
             	try { Class.forName("oracle.jdbc.driver.OracleDriver"); }
		catch (ClassNotFoundException ee) { ee.printStackTrace(); System.exit(-1); }
		// Method 2:
		//  DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
		// ---------------------------------------------------------------------------

		cnn = DriverManager.getConnection(url, user, passwd);
		stmt = cnn.createStatement();
		stmt2 = cnn.createStatement();
	   } catch (SQLException e ) { e.printStackTrace(); System.exit(-1); }
	}

	public static void main(String argv[]) {
		String user = null, passwd = null;
		user = ScreenIO.promptForString  ("User Name: ");
		passwd = ScreenIO.promptForString("Password : ");

		CleanStudentAccount appl = new CleanStudentAccount (user, passwd); // get connected, and create stmt.
	 	  System.out.println("\n\bNotice that the primary index of a table/view cannot be dropped explicily.");
		  System.out.println(   "\bHowever, it will be deleted automatically when its table is deleted.");
		  System.out.println(   "\bThe foreign key constraints must be dropped before some tables can be");
		  System.out.println(   "\bdropped. Use 'alter table tb_name drop constraint constr_name' to drop");
		  System.out.println(   "\bconstraints.\n");
		   
		  // System.out.println("\nLogin as: " + appl.user + "/" + appl.passwd);
		appl.deleteAll();

	}

        void deleteConstraints() {
		int constrCount = 0;
		String  tableName = "", constraintName = "", strAlter = "";
		String  sql = "";
		sql = "select constraint_name, table_name from user_constraints " +
			     " where constraint_type = \'R\' or constraint_type = \'C\'";
 
		try {
		    	res = stmt.executeQuery( sql );
			//	System.out.println("SQL string: " + sql);
		    	while ( res.next() ) {
				constraintName = res.getString(1);
				tableName = res.getString(2) ;
				strAlter = "alter table " + tableName + " drop constraint " + constraintName ;
				// System.out.println("alter string: " + strAlter);
				stmt2.execute(strAlter);
				constrCount ++;
			}
			if ( constrCount > 0 )
			   System.out.println("\n" + constrCount + " Referential and Check Constraints are dropped.");
			res.close();
		} catch (SQLException e2) { e2.printStackTrace() ; }
	}

        void deleteAll() {

		String	sql = null, objName = null, objType = null;
		char    ch;
		boolean deletionSucceeded = false;

		try {
		    res2 = stmt2.executeQuery("select count(*) from user_objects");
		    res2.next();
		    numberOfObjects = res2.getInt(1);
		    System.out.println("Number of objects to be deleted: " + numberOfObjects);
		    ch = ScreenIO.promptForChar("Continue to delete all objects [Y/N] ... ");

		    if ( ch != 'y' && ch != 'Y' ) System.exit(0);

		    // Drop constraints before dropping some schema objects such as table, view and stored procedures.
		    deleteConstraints();
		    // The loop will stop if (1) no object to delete or (2) nothing is deleted in a round.
		    while ( numberOfObjects > 0 && atLeastOneDeleted ) { 
		    	res = stmt.executeQuery("select object_type, object_name from user_objects");
			round ++;
			atLeastOneDeleted = false;
		        clearObjectCounts();
		    	while ( res.next() ) {
				objName = res.getString(2);
				objType = res.getString(1) ;
				deletionSucceeded = deleteOneObject( objType, objName );
				if ( deletionSucceeded )
					increaseObjectCounts(deletedCounts, objType);
				else    increaseObjectCounts(undeletedCounts, objType);
		    	}
			res2.close();
			res.close();
			printObjectCounts();
		    	res2 = stmt2.executeQuery("select count(*) from user_objects");
		    	res2.next();
		    	numberOfObjects = res2.getInt(1);
		    }
		    round ++;
		    printObjectCounts();
		    if ( numberOfObjects > 0 ) listUndeletedObjects();
		    deallocateResources();
		} catch (SQLException e2) { e2.printStackTrace() ; }
	}

	void listUndeletedObjects() {	
	   try {
		System.out.println("\n\tThe following objects cannot be deleted due to reasons such as external references: ");
		res = stmt.executeQuery("select object_type, object_name from user_objects");
		while (res.next() ) 
			System.out.println("\t" + res.getString(2) + "[" + res.getString(1) + "]");
	   } catch (SQLException e) { }
	}
 
	boolean deleteOneObject( String objType, String objName ) {
	   String sql = "";
	   try {
		sql = "DROP " + objType + " " + objName ;
		stmt2.execute(sql);
	   } catch (SQLException e) {	// System.out.println(sql +  " is not executed successfully.");
					// e.printStackTrace();
					return false; }
	   atLeastOneDeleted = true;
	   return true;
	}

	void deallocateResources() {
	   try {
		if (stmt != null ) stmt.close();
		if (stmt2 != null ) stmt2.close();
		if ( res != null ) res.close();
		if ( res2 != null ) res2.close();
		if ( cnn != null ) cnn.close();
	   } catch (SQLException e) {}
	   try { super.finalize(); } catch (Throwable e) {}
	}
}
